/*=======================================================================================
	LiquidAssets.do

		Create liquid assets 

	Author: Lorenz Kueng, June 2015
	
	NOTE: Some variable names in this file have been supressed for confidentiality reasons 
=========================================================================================*/

cap log close PFW_01
log using "$homedir/log-files/PFW_01_$date.log", text replace name(PFW_01)


*=============================================================
* Liquid Assets
*=============================================================


*-------------------------------------------------
* account_bank.dta : bank account balances 
*-------------------------------------------------

use "$PFWdata/Alaska/Sample Database/Final/account_bank.dta", clear
	
	rename YYYYYYbankaccounttype bankaccounttype
	tabmiss  bankaccounttype
	replace  bankaccounttype = userbankaccounttype if bankaccounttype==.
	replace  bankaccounttype = XXXXbankaccounttype if bankaccounttype==.
	tabmiss  bankaccounttype
	tabulate bankaccounttype, sort
	
	duplicates report userid bankaccounttype availablebalance 
	sum availablebalance, detail
	bysort bankaccounttype: sum availablebalance, detail

	collapse (sum) availablebalance, by(userid)
	
	rename  availablebalance   bank_balance 
	
	
	***Replace negative account balances with missing values
	
	count
	count if  bank_balance<0
	summarize bank_balance, detail
	summarize bank_balance if bank_balance<0, detail
	replace   bank_balance=.  if bank_balance<0 // less than 1%
	lab var   bank_balance "total bank balances (from account_bank)"
	summarize bank_balance, detail
	
	tempfile account_bank
	compress
	sort userid
	save `account_bank'


*-------------------------------------------------
* account_investment.dta : non-bank investment accounts
*-------------------------------------------------

use "$PFWdata/Alaska/Sample Database/Final/account_investment.dta", clear

	foreach name in totalbalance cash totalvestedbalance totalunvestedbalance marginbalance investmentaccounttype accountnumber totalbalancecurrency totalvestedbalancecurrency totalunvestedbalancecurrency marginbalancecurrency cashcurrency {
		cap n: rename yod`name' `name'
	}
	
	drop if totalbalance==. | investmentaccounttype==.
	
	duplicates report userid totalbalance
	
	tabulate investmentaccounttype, sort
	sum totalbalance, detail
	bysort investmentaccounttype: sum totalbalance, detail // only has categories 8 and 66 (taxable brokerage accounts)
	
	
	***Split into liquid assets, ie taxable accounts (about 55% of accounts), and illiquid assets, ie tax-deferred accounts

	generate brokerage = totalbalance if investmentaccounttype== 66	| ///	TAXABLE
										 investmentaccounttype==  8	  //	TAXABLE_BROKERAGE
										 
	generate ret_savings = totalbalance if  investmentaccounttype==	 1	| ///	401K
											investmentaccounttype==	 2	| ///	403B
											investmentaccounttype==	 4	| ///	529
											investmentaccounttype==	32	| ///	TRADITIONAL_IRA
											investmentaccounttype==	46	| ///	ROLLOVER_IRA
											investmentaccounttype==	47	| ///	ROTH_IRA
											investmentaccounttype==	50	| ///	SEP
											investmentaccounttype==	67	| ///	NON_TAXABLE
											investmentaccounttype==	68	| ///	EMPLOYER
											investmentaccounttype==	40	  //	OTHER
	tabmiss brokerage ret_savings
	
	collapse (sum) brokerage ret_savings, by(userid)

	
	***Replace negative account balances with missing values
	
	foreach var in brokerage ret_savings {
		display  "`var'"
		count
		count if  `var'<0
		summarize `var', detail
		summarize `var'   if `var'<0, detail
		replace   `var'=. if `var'<0 // less than 0.1%
	}
	lab var brokerage   "total balances of taxable (liquid) investment accounts (from account_investment)"
	lab var ret_savings "total balances of tax-deferred (illiquid) investment accounts (from account_investment)"

	summarize brokerage ret_savings, detail
	summarize brokerage   if brokerage  !=0, detail
	summarize ret_savings if ret_savings!=0, detail
	
	tempfile account_investment
	compress
	sort userid
	save `account_investment'



*-------------------------------------------------
* Combine assets
*-------------------------------------------------

use `account_bank', clear
merge 1:1 userid using `account_investment', nogen

	generate liquid_asset1 = bank_balance
	replace  liquid_asset1 = 0 if liquid_asset1==.
	lab var  liquid_asset1 "bank account balances"

	generate liquid_asset2 = liquid_asset1
	generate _temp = brokerage
	replace  _temp = 0 if _temp==.
	replace  liquid_asset2 = liquid_asset2 + _temp
	lab var  liquid_asset2 "bank + taxable brokerage account balances"
	drop _temp

	summarize liquid_asset*, detail
	
	compress
	sort userid
	save "$homedir/data/stata/PFW_LiquidAssets.dta", replace

log close PFW_01
